package com.qipay.mapper;


import com.qipay.order.config.OrderQueryEnum;
import com.qipay.order.config.OrderStateEnum;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.Map;

public class OrderSqlProvider extends com.qipay.baas.mapper.OrderSqlProvider {


    public String listCp0Tj(String from, String to, Long cpid) {
        String sql = "select a.cp_id as cpId,f.name as cpName, b.sum as allNum,a.sum as payNum,ROUND((a.sum/b.sum)*100,3) as successRate ,a.payAmount as payAmount,ROUND(a.perAmount) as perAmount,\n" +
                "a.maxAmount as maxAmount,ROUND((a.payAmount/c.total)*100,2) as total from  \n" +
                "(SELECT o.cp_id, COUNT(o.id) as sum, SUM(o.amount) as payAmount,AVG(o.amount) as perAmount,MAX(o.amount) as maxAmount FROM  `order` o  \n" +
                "WHERE  o.state =3 AND o.create_time>='%s' AND  o.create_time<='%s' GROUP BY cp_id)a,\n" +
                "(SELECT o.cp_id, COUNT(o.id) as sum  FROM  `order` o WHERE o.create_time>='%s' AND  o.create_time<='%s' GROUP BY cp_id) b,\n" +
                "(select SUM(a.payAmount) as total from \n" +
                "(SELECT o.cp_id, COUNT(o.id) as sum, SUM(o.amount) as payAmount,AVG(o.amount) as perAmount,MAX(o.amount) as maxAmount FROM  `order` o \n" +
                "WHERE  o.state =3 AND o.create_time>='%s' AND  o.create_time<='%s' GROUP BY cp_id)a,\n" +
                "(SELECT o.cp_id, COUNT(o.id) as sum  FROM  `order` o WHERE o.create_time>='%s' AND  o.create_time<='%s' GROUP BY cp_id) b,\n" +
                "cp_info f WHERE  a.cp_id =b.cp_id  and  a.cp_id =f.cp_id) c,cp_info f WHERE %s a.cp_id =b.cp_id  and  a.cp_id =f.cp_id order by payAmount desc";


        return String.format(sql, from, to, from, to, from, to, from, to, cpid == null ? "" : ("a.cp_id=" + cpid + " and"));
    }


    public String listSp0Tj(String from, String to, Long spid) {
        String sql = "select b.sp_id as spId,s.name as spName,s.bank as bank, b.sum as allNum,a.sum as payNum,ROUND((a.sum/b.sum)*100,2) as successRate," +
                "a.payAmount as payAmount,a.totalFee as totalFee,ROUND(a.perAmount) as perAmount,\n" +
                "a.maxAmount as maxAmount ,ROUND((a.payAmount/c.total)*100,2) as total from \n" +
                "(SELECT o.sp_id, COUNT(o.id) as sum, SUM(o.amount) as payAmount,SUM(o.fee) as totalFee,AVG(o.amount) as perAmount,MAX(o.amount) as maxAmount FROM  `order` o  \n" +
                "WHERE  o.state =3 AND o.create_time>='%s' AND  o.create_time<='%s' GROUP BY sp_id)a,\n" +
                "(SELECT o.sp_id, COUNT(o.id) as sum  FROM  `order` o WHERE o.create_time>='%s' AND  o.create_time<='%s' GROUP BY sp_id) b, \n" +
                "(select SUM(a.payAmount) as total from  \n" +
                "(SELECT o.sp_id, COUNT(o.id) as sum, SUM(o.amount) as payAmount,AVG(o.amount) as perAmount,MAX(o.amount) as maxAmount FROM  `order` o  \n" +
                "WHERE  o.state =3 AND o.create_time>='%s' AND  o.create_time<='%s' GROUP BY sp_id)a, \n" +
                "(SELECT o.sp_id, COUNT(o.id) as sum  FROM  `order` o WHERE o.create_time>='%s' AND  o.create_time<='%s' GROUP BY sp_id) b, \n" +
                "sp s WHERE  a.sp_id =b.sp_id  and  a.sp_id =s.id) c,sp s WHERE %s a.sp_id =b.sp_id  and  a.sp_id =s.id order by payAmount desc";

        return String.format(sql, from, to, from, to, from, to, from, to, spid == null ? "" : ("b.sp_id=" + spid + " AND"));
    }

    public String listChannel0Tj(String from, String to, int channelid) {
        String sql = "select c.name as channelType, b.sum as allNum,a.sum as payNum,ROUND((a.sum/b.sum)*100,2) as successRate,a.payAmount as payAmount,ROUND(a.perAmount) as perAmount,\n" +
                "a.maxAmount as maxAmount ,ROUND((a.payAmount/c.total)*100,2) as total from  \n" +
                "(SELECT o.channel_type, COUNT(o.id) as sum, SUM(o.amount) as payAmount,AVG(o.amount) as perAmount,MAX(o.amount) as maxAmount FROM  `order` o  \n" +
                "WHERE  o.state =3 AND o.create_time>='%s' AND  o.create_time<='%s' GROUP BY channel_type)a,\n" +
                "(SELECT o.channel_type, COUNT(o.id) as sum  FROM  `order` o WHERE o.create_time>='%s' AND  o.create_time<='%s' GROUP BY channel_type) b,\n" +
                "(select SUM(a.payAmount) as total from \n" +
                "(SELECT o.channel_type, COUNT(o.id) as sum, SUM(o.amount) as payAmount,AVG(o.amount) as perAmount,MAX(o.amount) as maxAmount FROM  `order` o  \n" +
                "WHERE  o.state =3 AND o.create_time>='%s' AND  o.create_time<='%s' GROUP BY channel_type)a,\n" +
                "(SELECT o.channel_type, COUNT(o.id) as sum  FROM  `order` o WHERE o.create_time>='%s' AND  o.create_time<='%s' GROUP BY channel_type) b,\n" +
                "channel_type c WHERE  a.channel_type =b.channel_type  and  a.channel_type =c.id)c,\n" +
                "channel_type c WHERE %s  a.channel_type =b.channel_type  and  a.channel_type =c.id order by payAmount desc";
        channelid = Integer.valueOf(channelid);
        return String.format(sql, from, to, from, to, from, to, from, to, channelid == 0 ? "" : ("c.id=" + channelid + " and"));
    }

    public String listChannelSp0Tj(String from, String to, String spid, int channelid) {
        String sql = "select a.sp_id as spId,c.name as typeName ,s.`name` AS spName,s.bank AS bank, b.sum as allNum,a.sum as payNum,ROUND((a.sum/b.sum)*100,2) as successRate,a.payAmount as payAmount,ROUND(a.perAmount) as perAmount,\n" +
                "a.maxAmount as maxAmount ,ROUND((a.payAmount/c.total)*100,2) as total from \n" +
                "(SELECT o.channel_type ,o.sp_id, COUNT(o.id) as sum, SUM(o.amount) as payAmount,AVG(o.amount) as perAmount,MAX(o.amount) as maxAmount FROM  `order` o \n" +
                "WHERE  o.state =3 AND o.create_time>='%s' AND  o.create_time<='%s' GROUP BY channel_type,sp_id)a,\n" +
                "(SELECT o.channel_type ,o.sp_id, COUNT(o.id) as sum  FROM  `order` o WHERE o.create_time>='%s' AND  o.create_time<='%s' GROUP BY channel_type,sp_id) b,\n" +
                "(select SUM(a.payAmount) as total from \n" +
                "(SELECT o.channel_type ,o.sp_id, COUNT(o.id) as sum, SUM(o.amount) as payAmount,AVG(o.amount) as perAmount,MAX(o.amount) as maxAmount FROM  `order` o \n" +
                "WHERE  o.state =3 AND o.create_time>='%s' AND  o.create_time<='%s' GROUP BY channel_type,sp_id)a,\n" +
                "(SELECT o.channel_type ,o.sp_id, COUNT(o.id) as sum  FROM  `order` o WHERE o.create_time>='%s' AND  o.create_time<='%s' GROUP BY channel_type,sp_id) b,\n" +
                "channel_type c,sp s WHERE  a.sp_id =b.sp_id  and  a.sp_id =s.id and a.channel_type = c.id and a.channel_type=b.channel_type)c,\n" +
                "channel_type c,sp s WHERE %s %s a.sp_id=b.sp_id  and   a.sp_id =s.id and a.channel_type = c.id and a.channel_type=b.channel_type   order by spId desc,payAmount desc";

        return String.format(sql, from, to, from, to, from, to, from, to, spid == null ? "" : ("a.sp_id=" + spid + " and"), channelid == 0 ? "" : ("c.id=" + channelid + " and"));
    }

    public String listCp_Sp0Tj(String from, String to, String cpid, int channelid) {
        String sql = "select f.name as cpName,a.cp_id AS cpId ,c.`name` AS typeName, b.sum as allNum,a.sum as payNum,ROUND((a.sum/b.sum)*100,2) as successRate,a.payAmount as payAmount,\n" +
                "ROUND(a.perAmount) as perAmount,a.maxAmount as maxAmount,ROUND((a.payAmount/c.total)*100,2) as total from \n" +
                "(SELECT o.cp_id ,o.channel_type, COUNT(o.id) as sum, SUM(o.amount) as payAmount,AVG(o.amount) as perAmount,MAX(o.amount) as maxAmount FROM  `order` o \n" +
                "WHERE  o.state =3 AND o.create_time>='%s' AND  o.create_time<='%s' GROUP BY cp_id,channel_type)a,\n" +
                "(SELECT o.cp_id,o.channel_type, COUNT(o.id) as sum  FROM  `order` o WHERE o.create_time>='%s' AND  o.create_time<='%s' GROUP BY cp_id,channel_type) b,\n" +
                "(select SUM(a.payAmount) as total from \n" +
                "(SELECT o.cp_id ,o.channel_type, COUNT(o.id) as sum, SUM(o.amount) as payAmount,AVG(o.amount) as perAmount,MAX(o.amount) as maxAmount FROM  `order` o \n" +
                "WHERE  o.state =3 AND o.create_time>='%s' AND  o.create_time<='%s' GROUP BY cp_id,channel_type)a,\n" +
                "(SELECT o.cp_id,o.channel_type, COUNT(o.id) as sum  FROM  `order` o WHERE o.create_time>='%s' AND  o.create_time<='%s' GROUP BY cp_id,channel_type) b,\n" +
                "cp_info f,channel_type c WHERE  a.channel_type =b.channel_type  and  a.channel_type =c.id and a.cp_id = f.cp_id AND a.cp_id=b.cp_id)c,\n" +
                "cp_info f,channel_type c WHERE %s  %s a.channel_type =b.channel_type  \n" +
                "and  a.channel_type =c.id and a.cp_id = f.cp_id AND a.cp_id=b.cp_id  order by cpId desc,payAmount desc";

        return String.format(sql, from, to, from, to, from, to, from, to, cpid == null ? "" : (" a.cp_id=" + cpid + " and "), channelid == 0 ? "" : ("c.id=" + channelid + " and"));
    }

    /**
     * 查询订单总数量，总金额，总手续费
     *
     * @param querysql
     * @return
     */
    public String sumOrder(String querysql) {
        return String.format("select count(0) as total,IFNULL(sum(c.amount),0) as amountCount,IFNULL(sum(c.advance_amount),0) as advanceAmount, IFNULL(sum(c.fee),0)   as  feeCount from (%s) c;", querysql);
    }

    public String countBy(String querysql) {
        return String.format("select count(0) as total from (%s) c;", querysql);
    }

    //查询总金额
    public String amountCount(String querysql) {
        return String.format("select  IFNULL(sum(c.amount),0)    as  amountCount   from (%s) c;", querysql);
    }

    //查询总金额
    public String feeCount(String querysql) {
        return String.format("select  IFNULL(sum(c.fee),0)   as  feeCount   from (%s) c;", querysql);
    }

    public String listBy(String querysql, Object offset0, Object rowss0) {

        long offset = offset0 == null
                ? 0l : Long.valueOf(offset0.toString());
        long rows = rowss0 == null
                ? 10l : Long.valueOf(rowss0.toString());
        return String.format("%s order by id desc limit %s, %s;", querysql, offset, rows);
    }
    public String listBy0(String querysql,String orderBy) {
        return String.format("%s order by "+orderBy+";", querysql);
    }

    public String general(Map<String, Object> params) {

        String sql = "select o.*, ci.name as cpName, a.id as agentId, ai.name as agentName "
                + "from `order` o "
                + "inner join cp c on o.cp_id= c.id and c.account_state= 2 "
                + "left join cp_info ci on o.cp_id= ci.cp_id "
                + "left join cp a on c.agent_id= a.id and a.account_state= 2 "
                + "left join cp_info ai on a.id= ai.cp_id "
                + "where 1= 1 ";

        String param = "";
        if (MapUtils.isNotEmpty(params)) {

            if (params.containsKey(OrderQueryEnum.CP_ID.getParam())) {
                param += String.format(" and o.cp_id= %s", params.get(OrderQueryEnum.CP_ID.getParam()));
            }
            if (params.containsKey(OrderQueryEnum.SP_ID.getParam())) {
                param += String.format(" and o.sp_id= %s", params.get(OrderQueryEnum.SP_ID.getParam()));
            }
            if (params.containsKey(OrderQueryEnum.CP_NAME.getParam())) {
                param += String.format(" and ci.name like '%%%s%%'", params.get(OrderQueryEnum.CP_NAME.getParam()));
            }
            if (params.containsKey(OrderQueryEnum.AGENT_ID.getParam())) {
                param += String.format(" and c.agent_id = %s", params.get(OrderQueryEnum.AGENT_ID.getParam()));
            }
            if (params.containsKey(OrderQueryEnum.AGENT_NAME.getParam())) {
                param += String.format(" and ai.name like '%%%s%%'", params.get(OrderQueryEnum.AGENT_NAME.getParam()));
            }
            if (params.containsKey(OrderQueryEnum.CHANNEL.getParam())) {
                param += String.format(" and o.channel_type= %s", params.get(OrderQueryEnum.CHANNEL.getParam()));
            }
            if (params.containsKey(OrderQueryEnum.STATE.getParam())) {
                param += String.format(" and o.`state`= %s", params.get(OrderQueryEnum.STATE.getParam()));
            }
            if (params.containsKey(OrderQueryEnum.TRADE_NO.getParam())) {
                param += String.format(" and o.trade_no like '%%%s%%'", params.get(OrderQueryEnum.TRADE_NO.getParam()));
            }
            if (params.containsKey(OrderQueryEnum.OUT_TRADE_NO.getParam())) {
                param += String.format(" and o.out_trade_no like '%%%s%%'", params.get(OrderQueryEnum.OUT_TRADE_NO.getParam()));
            }
            if (params.containsKey(OrderQueryEnum.SP_TRADE_NO.getParam())) {
                param += String.format(" and o.sp_trade_no like '%%%s%%'", params.get(OrderQueryEnum.SP_TRADE_NO.getParam()));
            }
            if (params.containsKey(OrderQueryEnum.FROM.getParam())) {
                param += String.format(" and o.create_time>= '%s 00:00:00'", params.get(OrderQueryEnum.FROM.getParam()));
            }
            if (params.containsKey(OrderQueryEnum.TO.getParam())) {
                param += String.format(" and o.create_time<= '%s 23:59:59'", params.get(OrderQueryEnum.TO.getParam()));
            }
        }

        return sql += param;
    }

    public String statementBy(Long cpId, String from, String to, Integer channel) {

        // init
        String sql = "select * from `order` where 1= 1 and `state`= 3 ";
        String params = "";

        // query
        if (null != cpId) {
            params += String.format(" and cp_id = %s", cpId);
        }
        if (StringUtils.isNotBlank(from)) {
            params += String.format(" and finish_time >= '%s 00:00:00'", from);
        }
        if (StringUtils.isNotBlank(to)) {
            params += String.format(" and finish_time <= '%s 23:59:59'", to);
        }
        if (null != channel && !channel.equals(0)) {
            params += String.format(" and channel_type= %s", channel);
        }

        return sql + params;
    }

    public String statementAllBy(String from, String to, Long cpId, String cpName, Long agentId, String contract, Integer channel) {

        return String.format(
                "select o.*, ci.name as cpName, ai.name as agentName, ai.contacts " +
                        "from (select * from `order` where `state`= 3 and `channel_type`<> 99 %s %s %s %s %s) o " +
                        "left join cp_info ci on o.cp_id= ci.cp_id %s " +
                        "left join cp_info ai on o.agent_id= ai.cp_id %s ",
                StringUtils.isNotBlank(from) ? String.format(" and finish_time >= '%s 00:00:00'", from) : "",
                StringUtils.isNotBlank(to) ? String.format(" and finish_time <= '%s 23:59:59'", to) : "",
                null != cpId ? String.format(" and cp_id= %s ", cpId) : "",
                (null != channel && !channel.equals(0)) ? String.format(" and channel_type= %s ", channel) : "",
                null != agentId ? String.format(" and agent_id= %s ", agentId) : "",
                StringUtils.isNotBlank(cpName) ? String.format("and ci.name like '%%%s%%'", cpName) : "",
                StringUtils.isNotBlank(contract) ? String.format("and ai.contacts like '%%%s%%'", contract) : ""
        );
    }

    public String statementSpBy(String from, String to, Long spId, String spName, String service, Integer channel) {

        return String.format(
                "select o.*, s.name as spName, s.service, s.bank " +
                        " from (select * from `order` where `state`= 3 and `channel_type`<> 99 %s%s%s%s) o " +
                        " inner join sp s on o.sp_id= s.id %s%s ",

                StringUtils.isNotBlank(from) ? String.format(" and finish_time >= '%s 00:00:00'", from) : "",
                StringUtils.isNotBlank(to) ? String.format(" and finish_time <= '%s 23:59:59'", to) : "",
                null != spId ? String.format(" and sp_id= %s ", spId) : "",
                (null != channel && !channel.equals(0)) ? String.format(" and channel_type= %s ", channel) : "",
                StringUtils.isNotBlank(spName) ? String.format(" and s.name like '%%%s%%' ", spName) : "",
                StringUtils.isNotBlank(service) ? String.format(" and s.bank like '%%%s%%' ", service) : ""
        );
    }

    /**
     * 订单查询，条件转换成sql
     *
     * @return
     */
    public String selectSpUserOrders(Integer spId, String startTime, String endTime) {
        StringBuffer sql = new StringBuffer("");
        sql.append("select o.*, ci.name as cpName ");
        sql.append("from `order` o ");
        sql.append("inner join cp c on o.cp_id= c.id and c.account_state= 2 ");
        sql.append("left join cp_info ci on o.cp_id= ci.cp_id ");
        sql.append("where 1= 1 ");
        sql.append("and o.state =").append(OrderStateEnum.SUCCESS.getType());
        if (null != spId && spId != 0)
            sql.append(String.format(" and o.sp_id= %s", spId));
        if (StringUtils.isNotBlank(startTime)) {
            sql.append(String.format(" and o.create_time>= '%s'", startTime));
        }
        if (StringUtils.isNotBlank(endTime)) {
            sql.append(String.format(" and o.create_time<= '%s'", endTime));
        }
        return sql.toString();
    }

    /**
     * 订单查询，条件转换成sql
     *
     * @return
     */
    public String selectSpUserOrdersTotal(Integer spId, String startTime, String endTime) {
        StringBuffer sql = new StringBuffer("");
        sql.append("select sum(o.actual_pay_amount) as totalAmount," +
                "  count(*) as totalCount ");
        sql.append("from `order` o ");
        sql.append("inner join cp c on o.cp_id= c.id and c.account_state= 2 ");
        sql.append("left join cp_info ci on o.cp_id= ci.cp_id ");
        sql.append("where 1= 1 ");
        sql.append("and o.state =").append(OrderStateEnum.SUCCESS.getType());
        if (null != spId && spId != 0)
            sql.append(String.format(" and o.sp_id= %s", spId));
        if (StringUtils.isNotBlank(startTime)) {
            sql.append(String.format(" and o.create_time>= '%s'", startTime));
        }
        if (StringUtils.isNotBlank(endTime)) {
            sql.append(String.format(" and o.create_time<= '%s'", endTime));
        }
        return sql.toString();
    }
}